package com.heeexy.example.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class ExcelUtil {

    protected static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public static Map<Integer, Map<Integer, String>> readExcelContentz(MultipartFile file) throws Exception {
        Map<Integer, Map<Integer, String>> content = new HashMap<Integer, Map<Integer, String>>();
        // 上传文件名
        Workbook wb = getWb(file);
        if (wb == null) {
            throw new Exception();
        }
        Sheet sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        Row row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        DataFormatter dataForMatter = new DataFormatter();
        for (int i = 0; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<Integer, String> cellValue = new HashMap<Integer, String>();
            while (j < colNum) {
                //Object obj = getCellFormatValue(row.getCell(j));
                Cell cell = row.getCell(j);
                String str = dataForMatter.formatCellValue(cell);
                cellValue.put(j, str);
                j++;
            }
            content.put(i, cellValue);
        }
        return content;
    }

    /**
     * 解析网络的excle文件
     *
     * @param mf
     * @return
     */
    private static Workbook getWb(MultipartFile mf) {
        String filepath = mf.getOriginalFilename();
        String ext = filepath.substring(filepath.lastIndexOf("."));
        Workbook wb = null;
        try {
            InputStream is = mf.getInputStream();
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
        return wb;
    }

    /**
     * 解析本地文件
     *
     * @param file
     * @return
     * @throws Exception
     */
    public static Map<Integer, Map<Integer, String>> readExcelContent(File file) throws Exception {
        Map<Integer, Map<Integer, String>> content = new HashMap<>();
        // 上传文件名
        Workbook wb = getWb(file);
        if (wb == null) {
            throw new Exception();
        }
        Sheet sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        Row row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        DataFormatter dataForMatter = new DataFormatter();
        for (int i = 0; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<Integer, String> cellValue = new HashMap<>();
            while (j < colNum) {
                //Object obj = getCellFormatValue(row.getCell(j));
                Cell cell = row.getCell(j);
                String str = dataForMatter.formatCellValue(cell);
                cellValue.put(j, str);
                j++;
            }
            content.put(i, cellValue);
        }
        return content;
    }

    /**
     * 解析本地的excle文件
     *
     * @param file
     * @return
     */
    private static Workbook getWb(File file) {
        String filepath = file.getName();
        String ext = filepath.substring(filepath.lastIndexOf("."));
        Workbook wb = null;
        try (InputStream is = new FileInputStream(file)) {
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
        return wb;
    }

    /**
     * 判断文件是否是excel文件
     *
     * @param file
     * @return
     */
    public static boolean isExcel(MultipartFile file) {
        String name = file.getOriginalFilename();
        if (name == null) {
            return false;
        }
        int indexOf = name.lastIndexOf(".");
        int idx = indexOf < 0 ? name.length() : indexOf;
        String ext = name.substring(idx);
        return ".xls".equals(ext) || ".xlsx".equals(ext);
    }

    /**
     * 判断文件是否是Xlsx文件
     *
     * @param filename
     * @return
     */
    public static boolean isXlsx(String filename) {
        String ext = filename.substring(filename.lastIndexOf("."));
        if (".xlsx".equals(ext)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 判断文件是否是Xls文件
     *
     * @param filename
     * @return
     */
    public static Boolean isXls(String filename) {
        String ext = filename.substring(filename.lastIndexOf("."));
        if (".xls".equals(ext)) {
            return true;
        } else {
            return false;
        }
    }


    /**
     * Excel表格导出
     *
     * @param response    HttpServletResponse对象
     * @param excelDatas  Excel表格的数据，封装为List<List<String>>
     * @param sheetNames  sheet的名字
     * @param fileName    导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<List<List<String>>> excelDatas,
                                   List<String> sheetNames,
                                   String fileName,
                                   int columnWidth) throws IOException {
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        for (int i = 0; i < sheetNames.size(); i++) {
            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(sheetNames.get(i));

            //设置表格列宽度
            sheet.setDefaultColumnWidth(columnWidth);

            //写入List<List<String>>中的数据

            int rowIndex = 0;
            if (excelDatas.get(i) != null) {
                for (List<String> data : excelDatas.get(i)) {
                    //创建一个row行，然后自增1
                    HSSFRow row = sheet.createRow(rowIndex++);

                    //遍历添加本行数据
                    for (int j = 0; j < data.size(); j++) {
                        //创建一个单元格
                        HSSFCell cell = row.createCell(j);

                        //创建一个内容对象
                        HSSFRichTextString text = new HSSFRichTextString(data.get(j));

                        //将内容对象的文字内容写入到单元格中
                        cell.setCellValue(text);
                    }
                }
            }
        }

        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());

        //关闭workbook
        workbook.close();
    }


    /**
     * Excel表格导出
     *
     * @param excelDatas  Excel表格的数据，封装为List<List<String>>
     * @param sheetNames  sheet的名字
     * @param fileName    导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static InputStream exportExcelIutputStream(
            List<List<List<String>>> excelDatas,
            List<String> sheetNames,
            String fileName,
            int columnWidth) throws IOException {
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        for (int i = 0; i < sheetNames.size(); i++) {
            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(sheetNames.get(i));

            //设置表格列宽度
            sheet.setDefaultColumnWidth(columnWidth);

            //写入List<List<String>>中的数据

            int rowIndex = 0;
            if (excelDatas.get(i) != null) {
                for (List<String> data : excelDatas.get(i)) {
                    //创建一个row行，然后自增1
                    HSSFRow row = sheet.createRow(rowIndex++);

                    //遍历添加本行数据
                    for (int j = 0; j < data.size(); j++) {
                        //创建一个单元格
                        HSSFCell cell = row.createCell(j);

                        //创建一个内容对象
                        HSSFRichTextString text = new HSSFRichTextString(data.get(j));

                        //将内容对象的文字内容写入到单元格中
                        cell.setCellValue(text);
                    }
                }
            }
        }
        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        InputStream is = new ByteArrayInputStream(workbook.getBytes());
        //关闭workbook
        workbook.close();
        return is;
    }
}
